﻿public class TestStatements
{
    public static string correlatedSubQueryStmt = @"SELECT DISTINCT p.Name
        FROM AdventureWorks.Production.Product AS p 
        WHERE EXISTS
            (SELECT *
             FROM AdventureWorks.Production.ProductModel AS pm 
             WHERE p.ProductModelID = pm.ProductModelID
                   AND pm.Name LIKE 'Long-Sleeve Logo Jersey%');";

    public static string existentialSubQueryStmt = @"SELECT DISTINCT p.Name, p.Color
        FROM AdventureWorks.Production.Product AS p 
        WHERE EXISTS
            (SELECT *
             FROM AdventureWorks.Production.ProductModel AS pm 
             WHERE p.ProductModelID = pm.ProductModelID
                   AND pm.Name LIKE 'Long-Sleeve Logo Jersey%') and p.Color = 'Multi';";

    public static string inSubQueryStmt = @"SELECT DISTINCT Name
        FROM AdventureWorks.Production.Product a
        WHERE a.ProductModelID IN
            (SELECT ProductModelID 
             FROM AdventureWorks.Production.ProductModel b
             WHERE Name LIKE 'Long-Sleeve Logo Jersey%');";

    public static string selectStmt = @"SELECT ProductID, Name, Color
          FROM Production.Product
          WHERE Name LIKE ('%Frame%')
          AND Name LIKE ('HL%')
          AND Color = 'Red' ;";

    public static string inSelectStmt = @"SELECT ProductID, Name, Color
          FROM Production.Product
          WHERE Name IN ('Blade''', 'Crown Race', 'Spokes');";

    public static string bwtweenStmt = @"SELECT ProductID, Name, Color
          FROM Production.Product
          WHERE ProductID BETWEEN 725 AND 734;";

    public static string likeWithEscapeStmt = @"SELECT * 
          FROM Production.ProductPhoto
          WHERE LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;";

    public static string notInAndUnicode = @" SELECT AddressLine1, AddressLine2, City, PostalCode, CountryRegionCode  
          FROM Person.Address AS a
          JOIN Person.StateProvince AS s ON a.StateProvinceID = s.StateProvinceID
          WHERE CountryRegionCode NOT IN ('US')
          AND City LIKE N'Pa%' ;";

    public static string containsStmt = @"SELECT Description 
          FROM Production.ProductDescription 
          WHERE CONTAINS(Description, N'performance');";

    public static string freetextStmt = @"  SELECT Description 
          FROM Production.ProductDescription 
          WHERE FREETEXT(Description, 'performance');";

    public static string freeTextTableStmt = @" SELECT FT_TBL.Description
              ,KEY_TBL.RANK
          FROM Production.ProductDescription AS FT_TBL 
              INNER JOIN FREETEXTTABLE(Production.ProductDescription,
              Description, 
              'high level of performance') AS KEY_TBL
          ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
          ORDER BY RANK DESC";

    public static string updateStmt = @" UPDATE Production.Product
          SET Color = N'Metallic Red'
          WHERE Name LIKE N'Road-250%' AND Color = N'Red';";

    public static string simpleInsert = @"  INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,ModifiedDate)
          VALUES (N'Square Yards', N'Y2', GETDATE());";

    public static string complexInsert = @"INSERT INTO dbo.EmployeeSales
              OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
              SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
              FROM Sales.SalesPerson AS sp
              INNER JOIN Person.Person AS c
                  ON sp.BusinessEntityID = c.BusinessEntityID
              WHERE sp.SalesYTD > 250000.00
              ORDER BY sp.SalesYTD DESC;";

    public static string deleteStmt = @"DELETE Production.ProductCostHistory
          WHERE StandardCost BETWEEN 12.00 AND 14.00
                AND EndDate IS NULL;";

    public static string complexDeleteStmt = @"  DELETE FROM Sales.SalesPersonQuotaHistory 
          WHERE BusinessEntityID IN 
              (SELECT BusinessEntityID 
               FROM Sales.SalesPerson 
               WHERE SalesYTD > 2500000.00);";

    public static string complexDeleteStmt2 = @"  DELETE FROM Purchasing.PurchaseOrderDetail
          WHERE PurchaseOrderDetailID IN
             (SELECT TOP (10) PurchaseOrderDetailID 
              FROM Purchasing.PurchaseOrderDetail 
              ORDER BY DueDate ASC);";

    public static string complexTopStmt = @"SELECT TOP(1) Model, Color, Price
          FROM dbo.Cars
          WHERE Color = 'red'
          UNION ALL
          SELECT TOP(1) Model, Color, Price
          FROM dbo.Cars
          WHERE Color = 'blue'
          ORDER BY Price ASC";

    public static string IntelaTracStmt = @"SELECT ITEM_ID, count(item_id) AS item_count FROM SATV_REP_EXCEPTION WHERE  ( BASE_ID = '8d79c2fb-ccd2-4d3c-88d4-51a68c3a7fe7'  )  
        AND  ( CRITICALITY_ID = '8fbc1422-6a6c-4fbd-912e-4e787a7c4c3e'  OR CRITICALITY_ID = 'd94d36ef-0a0f-451d-be1e-9ee6780340ea'  
        OR CRITICALITY_ID = 'f578ae6b-631d-406b-a88f-8ebcdf05cf37'  OR CRITICALITY_ID = '4369313b-79f9-4ab3-bafc-a678fe269a85'  
        OR CRITICALITY_ID = 'f4416fe0-b060-4011-acf1-226de7b71e9e'  OR CRITICALITY_ID = '8dfd48ae-4796-499a-810b-a852b3d14a14'  
        OR CRITICALITY_ID = 'c1fc3129-fe11-446a-8a1b-35f66b845120'  OR CRITICALITY_ID = '4ad2d5c3-cadd-4f87-beac-daea6f9b65c6'  
        OR CRITICALITY_ID = '8e44cb8f-7de5-4ba7-82db-553694ab680e'  OR CRITICALITY_ID = 'af2b9b59-d35c-4610-9c46-44e510164e84'  
        OR CRITICALITY_ID = 'fd594bf9-5433-4a4a-a07a-aee5c211c6fe'  OR CRITICALITY_ID = 'e65ca138-2e9d-4b4d-aeab-b90ad6ce3889'  
        OR CRITICALITY_ID = '8adc8f94-28a2-4b2f-bc75-f865f2b6a5f9'  OR CRITICALITY_ID = 'e59414fe-02eb-488c-96f6-601c42449b81'  
        OR CRITICALITY_ID = '09f38126-03fd-4e10-b1c2-d204f62dbedb'  OR CRITICALITY_ID = '5b968b1c-24b8-4b38-ae17-450e7aa79fd5'  
        OR CRITICALITY_ID = 'f5af24ce-113d-4268-9a84-edacb356b953'  OR CRITICALITY_ID = 'ee0d539c-fb78-45ba-b839-3968055f1c0e'  
        OR CRITICALITY_ID = '6d4c4d91-750e-4695-ac09-df7d123dca59'  OR CRITICALITY_ID = '5b0d63c8-455d-45f1-bf52-1e9ebcc15c32'  
        OR CRITICALITY_ID = '2ef73b49-7044-4b79-aa35-b10ebf5b1e73'  OR CRITICALITY_ID = '2eeb510c-0f14-4dd8-ab44-992b4e7b6798'  
        OR CRITICALITY_ID = '6780b843-9885-441d-8a5e-ed1a7caa5348'  OR CRITICALITY_ID = 'b7fa13ce-dbaa-43cf-8805-8a7e21d3c3b0'  
        OR CRITICALITY_ID = '6847e1b2-12dc-446f-bc20-8cdd828070cc'  OR CRITICALITY_ID = '29617930-7276-414b-81a8-3e9fd7a2c817'  
        OR CRITICALITY_ID = 'f57bae98-ae26-4af0-8818-2639b766b747'  OR CRITICALITY_ID = 'b737df20-5257-4ca8-94f7-e9d840c7b17e'  
        OR CRITICALITY_ID = '33f9d08f-69ab-4522-b950-8b465d469622'  OR CRITICALITY_ID = '72bac60e-00ae-4cce-82e8-d7dfa5878950'  
        OR CRITICALITY_ID = 'c478a462-4938-4352-a801-1936062f8ea0'  OR CRITICALITY_ID = '379846ea-c726-4070-96a0-8e2d4c628546'  
        OR CRITICALITY_ID = '1a3fba34-d5a2-4ece-9a3c-f7be75c7aca0'  OR CRITICALITY_ID = '91ef84c6-66e1-4f23-b449-6766c3377e74'  
        OR CRITICALITY_ID = '63b53050-4c64-4758-8d93-4bcb7f8c832f'  OR CRITICALITY_ID = 'fb8819ca-a85f-48f4-bbc6-4e21e03e77bc'  
        OR CRITICALITY_ID = '31190219-818c-4e57-98b2-b321935cfdf2'  OR CRITICALITY_ID = '3d41c63c-77ec-40b7-b393-a47ab235e3f9'  
        OR CRITICALITY_ID = '8f9f497b-948e-4e75-b44b-342865575ac2'  )  
        AND  ( SEVERITY_ID = 'bc927e43-e80d-4cf7-bb27-5be3c338a2de'  OR SEVERITY_ID = '4299930a-60b7-4868-b899-e918b174edaa'  
        OR SEVERITY_ID = '659de364-2ed0-49db-a52f-ce00b5911dc9'  OR SEVERITY_ID = 'ed8820cd-d099-4fc7-a3e7-edd08bc75315'  
        OR SEVERITY_ID = 'd004c4d6-ba36-46fb-85fa-e9bc6faf6e5b'  OR SEVERITY_ID = '781847d0-1891-47c8-8395-b8723eb43898'  )  
        AND  (FILTER_DATE between '2014-12-01T00:00:00' AND '2014-12-07T23:59:59'  ) 
        AND  (TIME >= '00:00' AND TIME <= '23:59' )  GROUP BY ITEM_ID";

    public static string unionTest = @"SELECT  PERMISSION_CODE  FROM ISMGT_PERMISSION
        , ISMGT_USER  WHERE (ISMGT_PERMISSION.ENTITY_FK = ISMGT_USER.USER_PK)  AND (ISMGT_PERMISSION.APP_TYPE_FK = 7)  
        AND (LOWER(ISMGT_USER.LOGIN_ID) = 'admin') 
            UNION 
           SELECT DISTINCT P.PERMISSION_CODE  FROM ISMGT_DEPARTMENT D  INNER JOIN ISMGT_USER_DEPARTMENT_LINK DL 
                ON D.DEPARTMENT_PK = DL.DEPARTMENT_FK  INNER JOIN ISMGT_USER U ON DL.USER_FK = U.USER_PK  
                INNER JOIN ISMGT_PERMISSION P ON D.DEPARTMENT_PK = P.ENTITY_FK  WHERE (P.APP_TYPE_FK = 7)  
                AND (LOWER(U.LOGIN_ID) = 'admin')";

    public static string multiBatchTest = 
        @"SELECT * from Sales.SalesPerson where BusinessEntityID = 55;

        SELECT * 
                  FROM Production.ProductPhoto
                  WHERE LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;

        SELECT TOP(1) Model, Color, Price
                  FROM dbo.Cars
                  WHERE Color = 'red'
                  UNION ALL
                  SELECT TOP(1) Model, Color, Price
                  FROM dbo.Cars
                  WHERE Color = 'blue'
                  ORDER BY Price ASC";

    public static string InjectStmt = @"
            Select * from Person.Address
                where PostalCode = '98011'+'or 1=1--'";
}
